/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.absisgroup.nsc.home;

import com.absisgroup.nsc.users.UsersEntity;
import com.absisgroup.nsc.utilities.DBUtilities;
import com.absisgroup.nsc.utilities.GenericServlet;
import com.absisgroup.nsc.utilities.Util;
import java.io.ByteArrayOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.json.JSONArray;
import org.json.JSONObject;
import org.w3c.dom.Attr;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
 *
 * @author Hue
 */
public class ExecuteQuery extends GenericServlet {

    @Override
    public int process(final Connection con, final HttpServletRequest request, final HttpServletResponse response) throws Exception {
        final String query = request.getParameter("query");
        if(query == null || query.isEmpty()) {
            return DONT_RUN_JSP;
        }
        final String parameters = request.getParameter("parameters");
        JSONArray jsParams = null;
        if(parameters != null && !parameters.isEmpty()) {
            jsParams = new JSONArray(parameters);
        }
        else {
            jsParams = new JSONArray();
        }
        final String datePattern = getMessage(request, "FORMAT.DATE");
        final String nqueCode = request.getParameter("nqueCode");
        final UsersEntity user = getCurrentUser(request);
        DBUtilities.enableDbmsOutput(con, 20000);
        final String sessionId = setEnvironmentVariables(con, user, nqueCode);
        final String result = execute(con, query, jsParams, datePattern, sessionId);
        writeToResponse(response, result);
        
        return DONT_RUN_JSP;
    }
    
    private String execute(final Connection con, final String strQuery, final JSONArray jsParams, final String datePattern, final String sessionId) throws Exception {
        debug();
        /*
        final PreparedStatement pStmt = con.prepareStatement(strQuery);
        final int iLength = jsParams.length();
        int iIndex = 1;
        for(int i=0; i<iLength; i++) {
            final JSONObject jsObj = jsParams.getJSONObject(i);
            final String dataType = jsObj.getString("datatype");
            if("DATE".equals(dataType)) {
                pStmt.setDate(iIndex++, Util.toSQLDate(jsObj.getString("value"), datePattern));
            }
            else if("NUMBER".equals(dataType)) {
                pStmt.setBigDecimal(iIndex++, Util.toBigDecimal(jsObj.getString("value")));
            }
            else {
                pStmt.setString(iIndex++, jsObj.getString("value"));
            }
        }
        final ResultSet rs = pStmt.executeQuery();
        while(rs.next()) {
            System.out.println(rs.getString(1));
        }
        pStmt.close();
        */
        final String output = DBUtilities.printDbmsOutput(con);
        
        final JSONObject jsObj = new JSONObject();
        jsObj.put("success", true);
        jsObj.put("output", output);
        jsObj.put("msg", "");

        return jsObj.toString();
    }
    
    private String setEnvironmentVariables(final Connection con, final UsersEntity user, final String nqueCode) throws Exception {
        final StringBuilder sb = new StringBuilder();
        if(nqueCode != null && !nqueCode.isEmpty()) {
            sb.append(nqueCode).append('_');
        }
        else {
            sb.append("NSCQUERY_");
        }
        sb.append(user.getUsrLogin()).append('_').append(Util.toString(new java.util.Date(), "yyyyMMdd_HHmmss"));
        
        final String sessionId = sb.toString();

        String sql = "{ call BO_AUDIT.init('NETIKA_AUDIT','NSC',?) }";
        java.sql.CallableStatement stmt = con.prepareCall(sql);
        stmt.setString(1, user.getUsrLogin());
        stmt.executeUpdate();
        stmt.close();
        
        sql = "{ call BO_AUDIT.setContext(?) }";
        stmt = con.prepareCall(sql);
        stmt.setString(1, sessionId);
        stmt.executeUpdate();
        stmt.close();

        sql = "{ call bo_log_manager.set_session_name(?) }";
        stmt = con.prepareCall(sql);
        stmt.setString(1, sessionId);
        stmt.executeUpdate();
        stmt.close();
        
        return sessionId;
    }
    
    private void debug() throws Exception {
        final DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
        final DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
        // root elements
        final Document doc = docBuilder.newDocument();
        doc.setXmlStandalone(true);
        final Element rootElement = doc.createElement("company");
        doc.appendChild(rootElement);
        // staff elements
        Element staff = doc.createElement("Staff");
        rootElement.appendChild(staff);
        // set attribute to staff element
        Attr attr = doc.createAttribute("id");
        attr.setValue("1");
        staff.setAttributeNode(attr);
        // shorten way
        // staff.setAttribute("id", "1");
        // firstname elements
        Element firstname = doc.createElement("firstname");
        firstname.appendChild(doc.createTextNode("yong"));
        staff.appendChild(firstname);
        // lastname elements
        Element lastname = doc.createElement("lastname");
        lastname.appendChild(doc.createTextNode("mook kim"));
        staff.appendChild(lastname);
        // nickname elements
        Element nickname = doc.createElement("nickname");
        nickname.appendChild(doc.createTextNode("mkyong"));
        staff.appendChild(nickname);
        // salary elements
        Element salary = doc.createElement("salary");
        salary.appendChild(doc.createTextNode("100000"));
        staff.appendChild(salary);
        // write the content into xml file
        final TransformerFactory transformerFactory = TransformerFactory.newInstance();
        final Transformer transformer = transformerFactory.newTransformer();
        transformer.setOutputProperty(OutputKeys.INDENT, "yes");
        transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");
        final DOMSource source = new DOMSource(doc);
        final ByteArrayOutputStream baOut = new ByteArrayOutputStream();
        final StreamResult result = new StreamResult(baOut);
        transformer.transform(source, result);
        Util.writeFile("C:/file.xml", baOut.toByteArray());
        baOut.close();
        System.out.println("File saved!");
    }
  
}
